/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



import java.io.BufferedReader;
import java.io.FileReader;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Andry
 */
public class TokoServiceJDBC implements TokoService{


    private static final String sqlInsert
            ="insert into produk (kode,judul,pengarang,penerbit,harga)"+
            "values (?,?,?,?,?)";

    private static final String sqlInsertPenjualan
            ="insert into penjualan (waktu,kasir,pembayaran)"+
            "values (?,?,?)";

    private static final String sqlSelectById
            ="select * from produk where id=?";

    private static final String sqlSelectByKode
            ="select * from produk where kode like ?";

    private static final String sqlSelectByJudul
            ="select * from produk where judul like ?";

    private static final String sqlSelectByPengarang
            ="select * from produk where pengarang like ?";

    private static final String sqlSelectByPenerbit
            ="select * from produk where penerbit like ?";

    private static final String sqlDelete
            ="delete from produk where id=?";

    private static final String sqlImportFile
            = "LOAD DATA INFILE ? INTO TABLE produk FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'(kode, judul, pengarang, penerbit, harga)";

    private Connection connect;
    private PreparedStatement psInsert;
    private PreparedStatement psInsertPenjualan;
    private PreparedStatement psSelectById;
    private PreparedStatement psSelectByKode;
    private PreparedStatement psSelectByJudul;
    private PreparedStatement psSelectByPengarang;
    private PreparedStatement psSelectByPenerbit;
    private PreparedStatement psDelete;
    private PreparedStatement psImportFile;



    public TokoServiceJDBC(Connection connect) throws SQLException{
    this.connect = connect;
    psInsert = this.connect.prepareStatement(sqlInsert);
    psInsertPenjualan = this.connect.prepareStatement(sqlInsertPenjualan);
    psSelectById = this.connect.prepareStatement(sqlSelectById);
    psSelectByKode = this.connect.prepareStatement(sqlSelectByKode);
    psSelectByJudul = this.connect.prepareStatement(sqlSelectByJudul);
    psSelectByPengarang = this.connect.prepareStatement(sqlSelectByPengarang);
    psSelectByPenerbit = this.connect.prepareStatement(sqlSelectByPenerbit);
    psDelete = this.connect.prepareStatement(sqlDelete);
    psImportFile = this.connect.prepareStatement(sqlImportFile);
    }

    public void simpan(Buku buku) {
        try {
            psInsert.setString(1, buku.getKode());
            psInsert.setString(2, buku.getJudul());
            psInsert.setString(3, buku.getPengarang());
            psInsert.setString(4, buku.getPenerbit());
            psInsert.setBigDecimal(5, buku.getHarga());
            psInsert.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(TokoServiceJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void hapus(Buku buku) {
        try {
            psDelete.setInt(1, buku.getId());
            psDelete.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    

    public List<Buku> cariKode(String kode) {
        try{
			List<Buku> hasil = new ArrayList<Buku>();
			psSelectByKode.setString(1, "%"+kode+"%");
			ResultSet rs =psSelectByKode.executeQuery();
			while(rs.next()){
				Buku b = konversiResultSetMenjadiBuku(rs);
				hasil.add(b);
			}
			return hasil;
		}catch (Exception e){
			e.printStackTrace();
		}
		return null;
    }

    public List<Buku> cariJudul(String judul) {
       try{
			List<Buku> hasil = new ArrayList<Buku>();
			psSelectByJudul.setString(1, "%"+judul+"%");
			ResultSet rs =psSelectByJudul.executeQuery();
			while(rs.next()){
				Buku b = konversiResultSetMenjadiBuku(rs);
				hasil.add(b);
			}
			return hasil;
		}catch (Exception e){
			e.printStackTrace();
		}
		return null;
    }

    public List<Buku> cariPengarang(String pengarang){
        try{
                        List<Buku> hasil = new ArrayList<Buku>();
			psSelectByPengarang.setString(1, "%"+pengarang+"%");
			ResultSet rs =psSelectByPengarang.executeQuery();
			while(rs.next()){
				Buku b = konversiResultSetMenjadiBuku(rs);
				hasil.add(b);
			}
			return hasil;

        } catch (Exception e) {
                 e.printStackTrace();
        }
        return null;
    }


    public List<Buku> cariPenerbit(String penerbit){
        try {
                List hasil = new ArrayList<Buku>();
                psSelectByPenerbit.setString(1, "%"+penerbit+"%");
                ResultSet rs = psSelectByPenerbit.executeQuery();
                while(rs.next()){
                    Buku b = konversiResultSetMenjadiBuku(rs);
                    hasil.add(b);
                }return hasil;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public Buku cariID(Integer id){
       try{
			psSelectById.setInt(1,id);
			ResultSet rs =psSelectById.executeQuery();

			//kalau next false, berarti tidak ada record yang ditemukan
			if(!rs.next()) return null;

			Buku p = konversiResultSetMenjadiBuku(rs);
			return p;
		}catch (Exception e){
			e.printStackTrace();
			return null;
		}
	}

	private Buku konversiResultSetMenjadiBuku(ResultSet rs) throws Exception{
		// TODO Auto-generated method stub
		Buku p = new Buku();
		p.setId(rs.getInt("id"));
		p.setKode(rs.getString("kode"));
		p.setJudul(rs.getString("judul"));
                p.setPengarang(rs.getString("pengarang"));
		p.setPenerbit(rs.getString("penerbit"));
		p.setHarga(rs.getBigDecimal("harga"));
		return p;
	}

    
    void importCsv(String file) throws SQLException {
        try
        {
            connect.setAutoCommit(false);// defaultnya true, kita buat false supaya bisa mengecek error. bila ga ada error baru di commit
            BufferedReader reader= new BufferedReader(new FileReader(file));
            String data = reader.readLine(); //mengskip header agar tidak d baca (langsung baris kedua)
            while((data=reader.readLine())!=null){
                Buku b = konversiCsvJadiBuku(data);
                simpan(b);
            }
            reader.close();
            connect.commit();
        }
        catch(Exception e) {
            try {
                System.out.println("Rool back, terjadi error");
                connect.rollback();
            } catch (Exception e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();            
        }finally {
            try {
                connect.setAutoCommit(true);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private Buku konversiCsvJadiBuku(String data) {
       String[]  bukuProperty = data.split(",");
       if(bukuProperty.length !=5) throw  new IllegalStateException("Invalid data");

       Buku b = new Buku();
       b.setKode(bukuProperty[0]);
       b.setJudul(bukuProperty[1]);
       b.setPengarang(bukuProperty[2]);
       b.setPenerbit(bukuProperty[3]);
       b.setHarga(new BigDecimal(bukuProperty[4]));
       return b;

    }

    void simpan(Penjualan p) {
        try {
            psInsertPenjualan.setDate(1, (Date) p.getWaktu());
            psInsertPenjualan.setString(2,p.getKasir());
            psInsertPenjualan.setBigDecimal(3, p.getPembayaran());
            psInsertPenjualan.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(TokoServiceJDBC.class.getName()).log(Level.SEVERE, null, ex);
        }
    }


}
